USE [GESTION_MORA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0007_ADHIERE_CODEUDOR]    Script Date: 11/23/2011 14:39:38 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_D0007_ADHIERE_CODEUDOR]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_D0007_ADHIERE_CODEUDOR]
GO

USE [GESTION_MORA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0007_ADHIERE_CODEUDOR]    Script Date: 11/23/2011 14:39:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WASP_D0007_ADHIERE_CODEUDOR]
AS

											
--select * from GmCuentas
truncate table tmp_cod

insert into tmp_cod
Select 
	cta  = p1cta 
	,oper = p1oper
	,suc = p1suc 
	,CODEUDOR1 = MAX(p1ndoc)  
	,CODEUDOR2 = MIN(p1ndoc) 
from gestion2.dbo.fsr012 (NOLOCK)
group by  
	p1cta, p1oper, p1suc
Update tmp_cod set CODEUDOR2 = null where CODEUDOR1 = CODEUDOR2

truncate table tmp_base

insert into tmp_base
select 
	R1cta
	,cuenta
	,Operacion
	,CODEUDOR1 = substring(CODEUDOR1, 1,len(CODEUDOR1) - 1) 	
	,CODEUDOR2 = substring(CODEUDOR2, 1,len(CODEUDOR2) - 1) 

from 
  GmCuentas Left Join gestion2.dbo.fsr011 on
   	cuenta    = r2cta and
	  Operacion = r2oper 
	Left Join tmp_cod on
    cuenta    = cta and
	  Operacion = oper
--	gestion2.dbo.fsr011,
--	GmCuentas,
--	tmp_cod
where
--	cuenta *= r2cta and
--	Operacion *= r2oper and
	relcod = 505 --and
--	cuenta*= cta and
--	Operacion *= oper 
GROUP BY R1CTA, CuenTA, Operacion, CODEUDOR1, CODEUDOR2	



truncate table GESTION_MORA.dbo.GMCtasEmpresas

insert into GESTION_MORA.dbo.GMCtasEmpresas
select 
	Cuenta_Empresa = R1cta
	,cuenta
	,Operacion
	,Cuenta_Aval1 = CODEUDOR1
	,Nombre_Aval1= rtrim(a.pfnom1) +' '+ rtrim(a.pfnom2)+' '+ rtrim(a.pfape1)+' '+rtrim(a.pfape2) 
	,Cuenta_Aval2 = CODEUDOR2
	,Nombre_Aval2 = rtrim(b.pfnom1) +' '+ rtrim(b.pfnom2)+' '+ rtrim(b.pfape1)+' '+rtrim(b.pfape2) 
from
    tmp_base Left Join gestion2.dbo.fsd002 a(nolock) ON
    	CODEUDOR1 = substring(a.pfndoc, 1, len(a.pfndoc) - 1)
    Left Join gestion2.dbo.fsd002 b(nolock) ON
      CODEUDOR2 = substring(b.pfndoc, 1, len(b.pfndoc) - 1)
--	tmp_base,
--	gestion2.dbo.fsd002 a(nolock),
--	gestion2.dbo.fsd002 b(nolock)
where
	a.pfndoc <>'' and
--	CODEUDOR1 *= substring(a.pfndoc, 1, len(a.pfndoc) - 1) and
	b.pfndoc <>'' --and
--	CODEUDOR2 *= substring(b.pfndoc, 1, len(b.pfndoc) - 1)
truncate table tmp_cod

truncate table tmp_base

											
GO

